<?php
header("content-type:text/json;charset=utf-8");
include("conn.php");
include("functions.php");
session_start();
date_default_timezone_set('prc');
require_once '../Classes/PHPExcel.php';
// 加载Excel文件
$inputFileName = '../score/'.$_POST['file_name'];
echo $inputFileName;
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
$create_time = date('Y-m-d H:i:s', time());
// 获取第一个工作表
$sheet = $objPHPExcel->getSheet(0);
$row = 1;
$col = 'A';
$cell = $sheet->getCell("$col$row");
$test_name = $cell->getValue();
$sql = "INSERT INTO rx_testlist (test_name, create_time, status) VALUES ('".$test_name."', '".$create_time."', '1')";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_execute($stmt);
$sql = "select max(id) from rx_testlist";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id);
$arrs = [];
$i = 0;
while (mysqli_stmt_fetch($stmt)) {
    $arrs[$i]["id"] = $id;
    $i++;
}
$test_id = $arrs[0]["id"];

$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();

for ($row = 4; $row <= $highestRow; $row++) {
    $student_id = "";
    $student_name = "";
    $class_rank = "";
    $grade_rank = "";
    $total_score = "";
    $chinese_score = "";
    $chinese_class_rank = "";
    $chinese_grade_rank = "";
    $math_score = "";
    $math_class_rank = "";
    $math_grade_rank = "";
    $english_score = "";
    $english_class_rank = "";
    $english_grade_rank = "";
    $politics_score = "";
    $politics_class_rank = "";
    $politics_grade_rank = "";
    $history_score = "";
    $history_class_rank = "";
    $history_grade_rank = "";
    $geography_score = "";
    $geography_class_rank = "";
    $geography_grade_rank = "";
    $physics_score = "";
    $physics_class_rank = "";
    $physics_grade_rank = "";
    $chemistry_score = "";
    $chemistry_class_rank = "";
    $chemistry_grade_rank = "";
    $biology_score = "";
    $biology_class_rank = "";
    $biology_grade_rank = "";
    $technology_score = "";
    $technology_class_rank = "";
    $technology_grade_rank = "";
    $japanese_score = "";
    $japanese_class_rank = "";
    $japanese_grade_rank = "";
    $upade_time = "";
    for ($col = 'A'; $col != $highestColumn; $col++) {
        $cell = $sheet->getCell("$col$row");
        $value = $cell->getValue();
        if($value == null) {
            $value = 0;
        }
        if($col == 'A') {
            $grade_rank = $value;
        }
        else if($col == 'B'){
            $student_name = $value;
        }
        else if($col == 'D') {
            $student_id = $value;
        }
        else if($col == 'F') {
            $class_rank = $value;
        }
        else if($col == 'G') {
            $total_score = $value;
        }
        else if($col == 'H') {
            $chinese_score = $value;
        }
        else if($col == 'I') {
            $chinese_class_rank = $value;
        }
        else if($col == 'J') {
            $chinese_grade_rank = $value;
        }
        else if($col == 'K') {
            $math_score = $value;
        }
        else if($col == 'L') {
            $math_class_rank = $value;
        }
        else if($col == 'M') {
            $math_grade_rank = $value;
        }
        else if($col == 'N') {
            $english_score = $value;
        }
        else if($col == 'O') {
            $english_class_rank = $value;
        }
        else if($col == 'P') {
            $english_grade_rank = $value;
        }
        else if($col == 'Q') {
            $politics_score = $value;
        }
        else if($col == 'R') {
            $politics_class_rank = $value;
        }
        else if($col == 'S') {
            $politics_grade_rank = $value;
        }
        else if($col == 'T') {
            $history_score = $value;
        }
        else if($col == 'U') {
            $history_class_rank = $value;
        }
        else if($col == 'V') {
            $history_grade_rank = $value;
        }
        else if($col == 'W') {
            $geography_score = $value;
        }
        else if($col == 'X') {
            $geography_class_rank = $value;
        }
        else if($col == 'Y') {
            $geography_grade_rank = $value;
        }
        else if($col == 'Z') {
            $physics_score = $value;
        }
        else if($col == 'AA') {
            $physics_class_rank = $value;
        }
        else if($col == 'AB') {
            $physics_grade_rank = $value;
        }
        else if($col == 'AC') {
            $chemistry_score = $value;
        }
        else if($col == 'AD') {
            $chemistry_class_rank = $value;
        }
        else if($col == 'AE') {
            $chemistry_grade_rank = $value;
        }
        else if($col == 'AF') {
            $biology_score = $value;
        }
        else if($col == 'AG') {
            $biology_class_rank = $value;
        }
        else if($col == 'AH') {
            $biology_grade_rank = $value;
        }
        else if($col == 'AI') {
            $technology_score = $value;
        }
        else if($col == 'AJ') {
            $technology_class_rank = $value;
        }
        else if($col == 'AK') {
            $technology_grade_rank = $value;
        }
        else if($col == 'AL') {
            $japanese_score = $value;
        }
        else if($col == 'AM') {
            $japanese_class_rank = $value;
        }
        else if($col == 'AN') {
            $japanese_grade_rank = $value;
        }
    }
    $upade_time = date('Y-m-d H:i:s', time());
    $sql = "INSERT INTO `rx_scorelist` (`test_id`, `student_id`, `student_name`, `class_rank`, `grade_rank`, 
    `total_score`, `chinese_score`, `chinese_class_rank`, `chinese_grade_rank`, `math_score`, `math_class_rank`, 
    `math_grade_rank`, `english_score`, `english_class_rank`, `english_grade_rank`, `politics_score`, `politics_class_rank`, 
    `politics_grade_rank`, `history_score`, `history_class_rank`, `history_grade_rank`, `geography_score`, `geography_class_rank`, 
    `geography_grade_rank`, `physics_score`, `physics_class_rank`, `physics_grade_rank`, `chemistry_score`, `chemistry_class_rank`, 
    `chemistry_grade_rank`, `biology_score`, `biology_class_rank`, `biology_grade_rank`, `technology_score`, `technology_class_rank`, 
    `technology_grade_rank`, `japanese_score`, `japanese_class_rank`, `japanese_grade_rank`, `upade_time`) 
    VALUES ('".$test_id."', '".$student_id."', '".$student_name."', '".$class_rank."', '".$grade_rank."', 
    '".$total_score."', '".$chinese_score."', '".$chinese_class_rank."', '".$chinese_grade_rank."', '".$math_score."', '".$math_class_rank."',
    '".$math_grade_rank."', '".$english_score."', '".$english_class_rank."', '".$english_grade_rank."', '".$politics_score."', '".$politics_class_rank."',
    '".$politics_grade_rank."', '".$history_score."', '".$history_class_rank."', '".$history_grade_rank."', '".$geography_score."', '".$geography_class_rank."',
    '".$geography_grade_rank."', '".$physics_score."', '".$physics_class_rank."', '".$physics_grade_rank."', '".$chemistry_score."', '".$chemistry_class_rank."',
    '".$chemistry_grade_rank."', '".$biology_score."', '".$biology_class_rank."', '".$biology_grade_rank."', '".$technology_score."', '".$technology_class_rank."',
    '".$technology_grade_rank."', '".$japanese_score."', '".$japanese_class_rank."', '".$japanese_grade_rank."', '".$upade_time."');";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_execute($stmt);
}

$code = 1;
$msg = "操作成功!";
mysqli_stmt_close($stmt);
mysqli_close($conn);
getApiResult($code, $arrs, $msg);
?>